<?php
/*
Plugin Name: Travel Expenses
Plugin URI: http://code.google.com/p/travelexpenseswp/
Description: Traveller's budget controller - check how much money you have spent (in total and daily)
Version: 1.0.2
Author: Jacek Karczmarczyk
Author URI: http://karczmarczyk.pl
*/

/*
Copyright 2010 Jacek Karczmarczyk

This program is free software: you can redistribute it and/or modify
it under the terms of the GNU General Public License as published by
the Free Software Foundation, either version 3 of the License, or
(at your option) any later version.

This program is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
GNU General Public License for more details.

You should have received a copy of the GNU General Public License
along with this program.  If not, see <http://www.gnu.org/licenses/>.
*/


/*
 * TODO:
 * - zapisywanie filtra w opcjach/sesji - ale data koncowa tylko jesli jest rozna od dzisiejszej, wpp null - latwe
 * - dane z X dni jseli nie podano obu dat
 * - ajax
 * - l10n
 */

class TravellerExpenses {
	const API_VERSION = "1.0.2";
	const DB_TABLE = "trex";

	private $_wpdb = null;

	public function __construct($wpdb) {
		$this->_wpdb = $wpdb;

		if ($this->installed()) {
			wp_register_script('jquery-autocomplete', WP_PLUGIN_URL.'/'.basename(dirname(__FILE__)).'/jquery.autocomplete.js');
			wp_register_script('trex-js', WP_PLUGIN_URL.'/'.basename(dirname(__FILE__)).'/trex.js');
			wp_register_style('trex-css', WP_PLUGIN_URL.'/'.basename(dirname(__FILE__)).'/trex.css');

			add_action('admin_menu', array($this, 'admin_menu'));
			add_filter('plugin_action_links', array($this, 'action_links'), 10, 2);

			add_filter('admin_init', array($this, 'admin_init'));
		}
	}

	public function installed() {
		$table_name = $this->_wpdb->prefix . self::DB_TABLE;
		return $this->_wpdb->get_var("show tables like '{$table_name}'") == $table_name;
	}

	/**
	 * Installs the trex plugin
	 *
	 */
	function install ()
	{
		if ($this->installed()) return;

		$table_name = $this->_wpdb->prefix . self::DB_TABLE;
		$sql = "CREATE TABLE " . $table_name . " (
			trex_id BIGINT( 20 ) NOT NULL AUTO_INCREMENT PRIMARY KEY  ,
			trex_date DATE NOT NULL,
			trex_name VARCHAR( 100 ) NOT NULL,
			trex_type VARCHAR( 100 ) NOT NULL,
			trex_amount INTEGER NOT NULL,
			trex_currency VARCHAR( 3 ) NOT NULL,
			trex_dir VARCHAR( 20 ) NOT NULL
		);";

		require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
		dbDelta($sql);

		add_option("trex_api_version", self::API_VERSION);
		add_option("trex_new_database", $sql);
		add_option("trex_default_interval", 3);
		add_option("trex_statistics_orientation", "horizontal");
		add_option("trex_flt_type", '');
		add_option("trex_flt_date_from", '');
		add_option("trex_flt_date_to", '');
	}

	public function uninstall() {
		if ($this->installed()) {
			$this->_wpdb->query("DROP TABLE IF EXISTS " . $this->_wpdb->prefix . self::DB_TABLE);
			delete_option("trex_api_version");
			delete_option("trex_new_database");
			delete_option("trex_default_interval");
			delete_option("trex_statistics_orientation");
			delete_option("trex_flt_type");
			delete_option("trex_flt_date_from");
			delete_option("trex_flt_date_to");
		}
	}

	function admin_init() {
		wp_enqueue_script('jquery-autocomplete');
		wp_enqueue_script('trex-js');
		wp_enqueue_style('trex-css');
	}

	/**
	 * Trex settings page
	 *
	 */
	function settings() {
		$trex_default_interval = get_option('trex_default_interval');
		$trex_statistics_orientation = get_option('trex_statistics_orientation');

		if (isset($_POST['trex_default_interval'])) {
			$trex_default_interval = (int)$_POST['trex_default_interval']>0 ? (int)$_POST['trex_default_interval'] : 3;
			$trex_statistics_orientation = $_POST['trex_statistics_orientation'] == 'horizontal' ? 'horizontal' : 'vertical';
			update_option('trex_default_interval', $trex_default_interval);
			update_option('trex_statistics_orientation', $trex_statistics_orientation);
			echo '<div class="updated"><p><strong>Configuration has been saved</strong></p></div>';
		}

		echo '<div class="wrap">';
		echo '<h2>Travel expenses settings</h2>';
		echo '<form name="form1" method="post" action="'.htmlspecialchars(str_replace( '%7E', '~', $_SERVER['REQUEST_URI'])).'">';
		echo '<p><em>By default the expenses from the last few days will be displayed</em><br/>';
		echo '<label for="trex_default_interval">Default interval</strong> <input type="text" id="trex_default_interval" name="trex_default_interval" value="'.htmlspecialchars($trex_default_interval).'" size="20" />';
		echo '</p>';
		echo '<p><em>In the statistics table types of expenses can be displayed vertically (as columns) or horizotally (as rows)</em><br/>';
		echo '<label for="trex_statistics_orientation">Statistics orientation</strong> <select id="trex_statistics_orientation" name="trex_statistics_orientation">';
		echo '<option value="vertical"'.($trex_statistics_orientation == 'vertical' ? ' selected="selected"' : '').'>vertical</option>';
		echo '<option value="horizontal"'.($trex_statistics_orientation == 'horizontal' ? ' selected="selected"' : '').'>horizontal</option>';
		echo '</select>';
		echo '</p>';
		echo '<p class="submit"><input type="submit" name="Submit" value="Update options »" /></p>';
		echo '</form>';
		echo '</div>';
	}

	/**
	 * Expenses management
	 *
	 */
	function manage() {
		if (!isset($_GET['flt_date_from'])) {
			$_GET['flt_date_from'] = date("Y-m-d", strtotime("-".get_option("trex_default_interval")." days"));
		}

		$queryString = "page=".htmlspecialchars($_GET['page'])."&amp;flt_type=".htmlspecialchars($_GET['flt_type'])."&amp;flt_date_from=".htmlspecialchars($_GET['flt_date_from'])."&amp;flt_date_to=".htmlspecialchars($_GET['flt_date_to']);

		echo '<div class="wrap"><h2>Manage travel expenses</h2>';

		if ($_GET['action'] === 'delete' && (int)$_GET['expense']) {
			$trex_id = (int)$_GET['expense'];
			if($_GET['sure']) {
				$this->_wpdb->query("DELETE FROM {$this->_wpdb->prefix}" . self::DB_TABLE . " WHERE trex_id=".$trex_id);
				echo '<div class="updated"><p><strong>Expense deleted!</strong></p></div>';
			} else {
				$expense = $this->_wpdb->get_row("SELECT * FROM {$this->_wpdb->prefix}" . self::DB_TABLE . " WHERE trex_id=".$trex_id);
				echo '<div class="updated"><p>You are about to delete "<strong>' . stripslashes($expense->trex_name.': '.$expense->trex_amount.' '.$expense->trex_currency) . '</strong>" ('.$expense->trex_date.').  Are you sure you want to do that?<br /><a href="admin.php?'.$queryString.'&action=delete&sure=yep&expense=' . $expense->trex_id . '">yes</a> | <a href="admin.php?'.$queryString.'">no</a></p></div>';
			}
		}
		else if ($_POST['sure'] === 'yep' && $_GET['action'] === 'save') {
			$trex_amount = str_replace(array('-',',',' '), array('', '.', ''), $_POST['trex_amount']);
			if (!$_POST['trex_income'] && $_POST['trex_amount'][0] == '+') {
				$trex_amount = -str_replace('+', '', $trex_amount);
			}

			$data = array(
				'trex_date'=>"'".date("Y-m-d", strtotime($_POST['trex_date']) ? strtotime($_POST['trex_date']) : time())."'",
				'trex_name'=>"'".$this->_wpdb->escape(trim($_POST['trex_name']))."'",
				'trex_type'=>"'".$this->_wpdb->escape(trim($_POST['trex_type']))."'",
				'trex_amount'=>(int)(100*$trex_amount),
				'trex_currency'=>"'".$this->_wpdb->escape(preg_replace('/[^A-Z]/', '', strtoupper(substr(trim($_POST['trex_currency']), 0, 3))))."'",
				'trex_dir'=>"'".$this->_wpdb->escape($_POST['trex_income'] ? 'income' : 'outcome')."'"
			);

			if ((int)$_GET['expense']) {
				foreach($data as $key=>$value) $data[$key] = "{$key} = $value";
				$this->_wpdb->query("UPDATE {$this->_wpdb->prefix}" . self::DB_TABLE . " SET ".implode(',', $data)." WHERE trex_id=".(int)$_GET['expense']);
				echo '<div class="updated"><p><strong>Expense has been saved!</strong></p></div>';
			} else {
				$this->_wpdb->query("INSERT INTO {$this->_wpdb->prefix}" . self::DB_TABLE . " (".implode(',', array_keys($data)).") values (".implode(',', $data).")");
				echo '<div class="updated"><p><strong>Expense has been added!</strong></p></div>';
			}
		}

		echo '<form id="trex_filter"><fieldset><legend>Filter</legend>';
		echo '<label>Type: <input type="text" name="flt_type" value="'.htmlspecialchars($_GET['flt_type']).'" /></label>';
		echo '<label style="margin-left: 5em">Date from: <input type="date" name="flt_date_from" value="'.htmlspecialchars($_GET['flt_date_from']).'" /></label>';
		echo '<label style="margin-left: 5em">Date to: <input type="date" name="flt_date_to" value="'.htmlspecialchars($_GET['flt_date_to']).'" /></label>';
		echo '<input type="submit" value="Filter" />';
		foreach($_GET as $key=>$val) {
			if (!in_array($key, array('flt_type', 'flt_date_from', 'flt_date_to'))) {
				echo '<input type="hidden" name="'.htmlspecialchars($key).'" value="'.htmlspecialchars($val).'" />';
			}
		}
		echo '</fieldset></form>';
		echo '
			<table class="widefat"><thead><tr>
				<th scope="col">Date</th>
				<th scope="col">Amount</th>
				<th scope="col">Expense name</th>
				<th scope="col">Expense type</th>
				<th scope="col" colspan="2" style="text-align: center;">Action</th>
				</tr></thead><tbody id="the-list">';

		$expenses = $this->get_expenses($_GET['flt_type'], $_GET['flt_date_from'], $_GET['flt_date_to']);
		$sum = array();
		$mindate = null;
		$maxdate = null;
		foreach ($expenses as $expense) {
			$newdate = $expense->trex_date;
			$newcurrency = $expense->trex_currency;

			if ($expense->trex_dir == 'outcome') {
				if (get_option("trex_statistics_orientation") == 'vertical') {
					$sum[$expense->trex_currency][$expense->trex_type ? $expense->trex_type : 'undefined'] += $expense->trex_amount;
				} else {
					$sum[$expense->trex_type ? $expense->trex_type : 'undefined'][$expense->trex_currency] += $expense->trex_amount;
				}

				if (is_null($mindate) || strtotime($expense->trex_date) < $mindate) {
					$mindate = strtotime($expense->trex_date);
				}

				if (is_null($maxdate) || strtotime($expense->trex_date) > $maxdate) {
					$maxdate = strtotime($expense->trex_date);
				}
			}

			$editamount = number_format(abs($expense->trex_amount)/100, 2, '.', '');
			if ($expense->trex_dir == 'outcome' && $expense->trex_amount < 0) {
				$editamount = "+".$editamount;
			}

			?>
			<tr class="trex-<?= ($expense->trex_dir == 'income' || $editamount[0] == '+') ? 'income' : 'outcome'; ?>">
			<td><input title="Date" name="trex_date" style="width: 8em" type="text" value="<?php echo htmlspecialchars($expense->trex_date); ?>" /></td>
			<td>
				<input title="Amount" name="trex_amount" type="text" style="width: 6em; text-align: right" value="<?php echo htmlspecialchars($editamount); ?>" />
				<input title="Currency" name="trex_currency" type="text" style="width: 3em" value="<?php echo htmlspecialchars($expense->trex_currency); ?>" />
				<label for="trex_income_<?= $expense->trex_id ?>" style="margin-left: 1em">Income: </label><input id="trex_income_<?= $expense->trex_id ?>" name="trex_income" type="checkbox" <?= $expense->trex_dir == 'income' ? ' checked="checked"' : '' ?> /></td>
			<td><input title="Expense name" name="trex_name" type="text" value="<?php echo htmlspecialchars($expense->trex_name); ?>" /></td>
			<td><input title="Expense type" name="trex_type" type="text" class="trex_type" style="width: 8em" value="<?php echo htmlspecialchars($expense->trex_type); ?>" /></td>
			<td><input type="button" class="trex_save" value="Save »" data-action="<?= '?'.$queryString.'&amp;action=save&amp;expense='.(int)$expense->trex_id ?>"></td>
			<td><input type="button" class="trex_delete" value="Delete" data-action="<?= '?'.$queryString.'&amp;action=delete&amp;sure=1&amp;expense='.(int)$expense->trex_id ?>"></td>
			</tr>
			<?php
		}

	?>

		<tr class="trex-new">
		<td><input title="Date" name="trex_date" type="text" style="width: 8em" value="<?= htmlspecialchars($newdate ? $newdate : date("Y-m-d")) ?>" /></td>
		<td>
			<input autofocus title="Amount" name="trex_amount" id="trex_amount_0" type="text" style="width: 6em; text-align: right" />
			<input title="Currency" name="trex_currency" type="text" style="width: 3em" value="<?= htmlspecialchars($newcurrency) ?>" />
			<label for="trex_income_0" style="margin-left: 1em">Income: </label><input id="trex_income_0" name="trex_income" type="checkbox" /><br/>
			<script type="text/javascript">('autofocus' in document.getElementById('trex_amount_0')) || document.getElementById('trex_amount_0').focus();</script>
		</td>
		<td><input title="Expense name" name="trex_name" type="text" /></td>
		<td><input title="Expense type" name="trex_type" class="trex_type" type="text" style="width: 8em" /></td>
		<td><input type="button" class="trex_save" value="Add »" data-action="<?= '?'.$queryString.'&amp;action=save' ?>"></td>
		<td></td>
		</tr>

	<?php
		echo "</tbody></table>\n";

		echo '<form id="trex_form" method="post"><div><input type="hidden" name="sure"><input type="hidden" name="trex_date"><input type="hidden" name="trex_amount"><input type="hidden" name="trex_currency"><input type="hidden" name="trex_income"><input type="hidden" name="trex_name"><input type="hidden" name="trex_type">'."</div></form>\n";

		if (sizeof($expenses)) {
			if (get_option("trex_statistics_orientation") == 'vertical') {
				$this->statistics_vertical($sum, 1+round(($maxdate-$mindate)/86400));
			} else {
				$this->statistics_horizontal($sum, 1+round(($maxdate-$mindate)/86400));
			}
		}

		echo '</div>';

		$types = $this->get_types();
		if (sizeof($types)) {
			echo '<script type="text/javascript">addLoadEvent(function () { jQuery("input.trex_type").autocomplete('.json_encode($types).', {autoFill: true}); });</script>';
		}
	}

	/**
	 * Returns filtered array of expenses
	 *
	 * @param string $type Type of expenses
	 * @param string $date_from Minimum date
	 * @param string $date_to Maximum date
	 * @return array Array of expenses
	 */
	function get_expenses($type, $date_from, $date_to) {
		if (strtotime($date_from)) $filter_date_from = "AND trex_date >= '".date("Y-m-d", strtotime($date_from))."'";
		if (strtotime($date_to)) $filter_date_to = "AND trex_date <= '".date("Y-m-d", strtotime($date_to))."'";
		if ($type) $filter_type = "AND trex_type = '".$this->_wpdb->escape($type)."'";

		return $this->_wpdb->get_results("SELECT * FROM ".$this->_wpdb->prefix . self::DB_TABLE . " WHERE 1=1 {$filter_type} {$filter_date_from} {$filter_date_to} ORDER BY trex_date, trex_id");
	}


	/**
	 * Formats currency amount
	 *
	 * @param integer $amount Amount * 100
	 * @param string $currency Currency
	 * @return string Formatted amount
	 */
	function format_currency($amount, $currency) {
		return number_format($amount/100, 0, ',', '').' '.$currency;
	}


	/**
	 * Prints statistics - vertical version
	 *
	 * @param array $sum Array of amount sums pre currency/type
	 * @param integer $days Interval in days
	 */
	function statistics_vertical($sum, $days) {
		$types = array();
		foreach($sum as $currency=>$currency_amounts) {
			foreach($currency_amounts as $type=>$amount) {
				$types[$type] = $type;
			}
		}

		ksort($sum);
		ksort($types);
		$balance = $this->get_balance_data();

		print "<h2>Summary for {$days} day(s)</h2>";
		print "<table class=\"widefat trex_vertical\" id=\"trex_statistics\">";
		print "<thead><tr>";
		foreach($types as $type) print "<th>".htmlspecialchars($type)."</th>";
		print "<th>Total expenses</th><th>Balance</th></tr></thead>";

		print "<tbody>";
		foreach($sum as $currency=>$currency_amounts) {
			print "<tr>";
			foreach($types as $type) {
				$amount = $currency_amounts[$type];

				if ($amount < 0) {
					$amount = -$amount;
					$sign = '+';
				}
				else {
					$sign = '';
				}
				print "<td>";
				if ($amount)
					print "<strong>".$sign.$this->format_currency($amount, $currency)."</strong><br/>".$sign.$this->format_currency($amount/$days, $currency).'/day';
				print "</td>";
			}

			print "<td class=\"trex_total\">";
			$amount = array_sum($currency_amounts);
			if ($amount < 0) {
				$amount = -$amount;
				$sign = '+';
			}
			else {
				$sign = '';
			}
			if ($amount)
				print "<strong>".$sign.$this->format_currency($amount, $currency)."</strong><br/>".$sign.$this->format_currency($amount/$days, $currency).'/day';
			print "</td>";

			print "<td class=\"trex_balance\"><strong>".(($balance[$currency] <= 0) ? '' : '+').$this->format_currency($balance[$currency], $currency)."</strong></td>";

			print "</tr>";
		}
		print "</tbody>";
		print "</table>";
	}

	/**
	 * Prints statistics - horizontal version
	 *
	 * @param array $sum Array of amount sums pre currency/type
	 * @param integer $days Interval in days
	 */
	function statistics_horizontal($sum, $days) {
		$types = array();
		foreach($sum as $type=>$type_amounts) {
			foreach($type_amounts as $currency=>$amount) {
				$currencies[$currency] = $currency;
				$currency_amounts[$currency] += $amount;
			}
		}

		ksort($sum);
		ksort($currencies);
		$balance = $this->get_balance_data();

		print "<h2>Summary for {$days} day(s)</h2>";
		print "<table class=\"widefat trex_horizontal\" id=\"trex_statistics\">";
		print "<thead><tr><th>Expence type</th>";
		foreach($currencies as $currency) print "<th>".htmlspecialchars($currency)."</th>";
		print "</thead>";

		print "<tbody>";
		foreach($sum as $type=>$type_amounts) {
			print "<tr><th scope=\"col\">".htmlspecialchars($type).':</th>';
			foreach($currencies as $currency) {
				$amount = $type_amounts[$currency];

				if ($amount < 0) {
					$amount = -$amount;
					$sign = '+';
				}
				else {
					$sign = '';
				}
				print "<td>";
				if ($amount)
					print "<strong>".$sign.$this->format_currency($amount, $currency)."</strong><br/>".$sign.$this->format_currency($amount/$days, $currency).'/day';
				print "</td>";
			}
			print "</tr>";
		}
		print "</tbody>";

		print "<tfoot>";
		print "<tr class=\"trex_total\"><th scope=\"col\">Total:</th>";
		print "<td>";
		foreach($currencies as $currency) {
			$amount = $currency_amounts[$currency];
			if ($amount < 0) {
				$amount = -$amount;
				$sign = '+';
			}
			else {
				$sign = '';
			}
			if ($amount)
				print "<strong>".$sign.$this->format_currency($amount, $currency)."</strong><br/>".$sign.$this->format_currency($amount/$days, $currency).'/day';
			print "</td>";
		}
		print "</tr>";
		print "<tr class=\"trex_balance\"><th scope=\"col\">Balance:</th>";
		foreach($currencies as $currency)
			print "<td><strong>".(($balance[$currency] <= 0) ? '' : '+').$this->format_currency($balance[$currency], $currency)."</strong></td>";
		print "</tr>";

		print "</table>";
	}


	/**
	 * Calculates current account balance in every currency
	 *
	 * @return array
	 */
	function get_balance_data() {
		$balance = array();
		$balancedata = $this->_wpdb->get_results($sql = "SELECT trex_currency, SUM(CASE WHEN trex_dir='income' THEN trex_amount ELSE -trex_amount END) AS sum FROM ".$this->_wpdb->prefix . self::DB_TABLE . " GROUP BY trex_currency");
		foreach($balancedata as $balanceitem) $balance[$balanceitem->trex_currency] = $balanceitem->sum;
		return $balance;
	}


	/**
	 * Returns all used types of expenses
	 *
	 * @return array
	 */
	function get_types() {
		$types = array();
		$typesdata = $this->_wpdb->get_results("SELECT DISTINCT trex_type FROM ".$this->_wpdb->prefix . self::DB_TABLE." WHERE trex_type <> '' ORDER BY trex_type");
		foreach($typesdata as $type) $types[] = $type->trex_type;
		return $types;
	}


	/**
	 * Adds admin pages
	 *
	 */
	function admin_menu() {
		add_options_page('Travel Expenses settings', 'Travel expenses', 10, 'trex_settings', array($this, 'settings'));
		add_utility_page('Travel expenses', 'Expenses', 8, 'trex_manage', array($this, 'manage'));
	}

	function action_links($links, $file) {
		if ($file == basename(dirname(__FILE__)).DIRECTORY_SEPARATOR.basename(__FILE__)) {
			array_unshift($links, '<a href="'.admin_url('options-general.php?page=trex_manage').'">'.__('Manage expenses').'</a>');
			array_unshift($links, '<a href="'.admin_url('options-general.php?page=trex_settings').'">'.__('Settings').'</a>');
		}
		return $links;
	}
}

$trex = new TravellerExpenses($GLOBALS['wpdb']);
register_activation_hook(__FILE__, array($trex, "install"));
register_uninstall_hook(__FILE__, array($gmtr, "uninstall"));

?>